import json
import time

from peewee import *
from sensi_filter import SensiwordFilter

db = MySQLDatabase("testdatabase", user='root', password='buaa2023', host='43.143.129.27',
                   port=3306)


class User(Model):
    '''记录用户'''
    id = AutoField(primary_key=True)
    username = CharField(unique=True)
    password = CharField()
    is_admin = BooleanField(default=False)

    class Meta:
        database = db


class UserGroup(Model):
    '''记录一个用户组'''
    id = AutoField(primary_key=True)
    group_name = CharField(unique=True)
    creator_id = ForeignKeyField(User)
    administrator_id = ForeignKeyField(User, default=creator_id)

    class Meta:
        database = db
        table_name = 'user_group'


class Question(Model):
    '''记录一个问题'''
    id = AutoField(primary_key=True)
    creator_id = ForeignKeyField(User)
    content = TextField()
    question_name = CharField()

    class Meta:
        database = db


class QuestionGroup(Model):
    '''记录一个问题组'''
    id = AutoField(primary_key=True)
    group_name = CharField(unique=True)
    creator_id = ForeignKeyField(User)
    is_public = BooleanField(default=True)
    tags = TextField(default='[]')

    class Meta:
        database = db
        table_name = 'question_group'


class SolutionHistory(Model):
    '''记录一条用户的做题记录'''
    id = AutoField(primary_key=True)
    user_id = ForeignKeyField(User, on_delete=True)
    question_id = ForeignKeyField(Question, on_delete=True)
    time = TimestampField()
    score = FloatField()
    user_answer = TextField()

    class Meta:
        database = db
        table_name = 'solution_history'


class ReUserToGroup(Model):
    '''记录用户与用户组的所属关系'''
    id = AutoField(primary_key=True)
    user_id = ForeignKeyField(User, on_delete=True)
    group_id = ForeignKeyField(UserGroup, on_delete=True)

    class Meta:
        database = db
        table_name = 're_user_to_group'
        constraints = [SQL('UNIQUE(user_id, group_id)')]


class ReQuestionToGroup(Model):
    '''记录问题与问题组的所属关系'''
    id = AutoField(primary_key=True)
    question_id = ForeignKeyField(Question, on_delete=True)
    group_id = ForeignKeyField(QuestionGroup, on_delete=True)

    class Meta:
        database = db
        table_name = 're_question_to_group'
        constraints = [SQL('UNIQUE(question_id, group_id)')]


class QuestionGroupPerm(Model):
    '''记录问题组的权限 user_group_id, question_group_id'''
    id = AutoField(primary_key=True)
    user_group_id = ForeignKeyField(UserGroup, on_delete=True)
    question_group_id = ForeignKeyField(QuestionGroup, on_delete=True)

    class Meta:
        database = db
        table_name = 'question_group_perm'
        constraints = [SQL('UNIQUE(user_group_id, question_group_id)')]


class SensitiveWord(Model):
    '''记录敏感词汇'''
    id = AutoField(primary_key=True)
    word = CharField(max_length=32)

    class Meta:
        database = db
        table_name = 'sensitive_word'


class Manager:
    def get_user(self, username):
        users = User.select().where(User.username == username)
        return users[0]

    def get_question_by_id(self, ques_id):
        ques = Question.select().where(Question.id == ques_id)
        return ques[0]

    def get_sensitive_words(self):
        return SensitiveWord.select()

    def get_user_byId(self, userId):
        users = User.select().where(User.id == userId)
        return users[0]

    def try_login(self, username, pwhash):
        users = User.select().where(User.username == username)
        if len(users) == 0:
            return False
        return users[0].password == pwhash

    def try_register(self, username, pwhash):
        users = User.select().where(User.username == username)
        if len(users) != 0:
            return False
        new_user = User(username=username, password=pwhash)
        new_user.save()
        return True

    def update_information(self, username, pwhash):
        users = User.select().where(User.username == username)
        if (len(users) == 0):
            return False, "未找到该用户"
        else:
            user = users[0]
        user.password = pwhash
        user.save()
        return True, "修改成功"

    def select_allowed_questiongroup_by_user(self, username):
        '''
        根据用户名，查看对该用户可见的问题组
        返回值为一个列表，列表中每个元素都有四个类变量，与QuestionGroup相同
        结果只包括非公开问题组
        '''
        results = (QuestionGroup
                   .select(QuestionGroup.id, QuestionGroup.group_name, QuestionGroup.creator_id,
                           QuestionGroup.is_public)
                   .join(QuestionGroupPerm, on=(QuestionGroupPerm.question_group_id == QuestionGroup.id))
                   .join(UserGroup, on=(QuestionGroupPerm.user_group_id == UserGroup.id))
                   .join(ReUserToGroup, on=(UserGroup.id == ReUserToGroup.group_id))
                   .join(User, on=(ReUserToGroup.user_id == User.id))
                   .where(User.username == username))
        result = [x for x in results]
        results = (QuestionGroup
                   .select(QuestionGroup.id, QuestionGroup.group_name, QuestionGroup.creator_id,
                           QuestionGroup.is_public)
                   .join(User, on=(User.id == QuestionGroup.creator_id))
                   .where(User.username == username))
        for x in results:
            result.append(x)
        return result

    def select_managed_questiongroup_by_user(self, username):
        '''
        根据用户名，查看对该用户可见的问题组
        返回值为一个列表，列表中每个元素都有四个类变量，与QuestionGroup相同
        结果只包括非公开问题组
        '''
        results = (QuestionGroup
                   .select(QuestionGroup.id, QuestionGroup.group_name, QuestionGroup.creator_id,
                           QuestionGroup.is_public)
                   .join(User, on=(User.id == QuestionGroup.creator_id))
                   .where(User.username == username))
        return results

    def select_allowed_questiongroup_by_usergroup(self, group_name):
        '''
        根据用户名，查看对某用户组可见的问题组
        返回值为一个列表，列表中每个元素都有四个类变量，与QuestionGroup相同
        结果只包括非公开问题组
        '''
        results = (QuestionGroup
                   .select(QuestionGroup.id, QuestionGroup.group_name, QuestionGroup.creator_id,
                           QuestionGroup.is_public)
                   .join(QuestionGroupPerm, on=(QuestionGroupPerm.question_group_id == QuestionGroup.id))
                   .join(UserGroup, on=(QuestionGroupPerm.user_group_id == UserGroup.id))
                   .where(UserGroup.group_name == group_name))
        return results

    def select_all_questiongroup(self):
        return QuestionGroup.select()

    def select_public_questiongroup(self) -> list[Question]:
        '''查找全部的公开问题组'''
        results = QuestionGroup.select().where(QuestionGroup.is_public == True)
        return results

    def select_sensitive_question(self):
        '''查找全部的带有敏感词的问题'''
        sensi_filter = SensiwordFilter()
        words = SensitiveWord.select()
        for word in words:
            sensi_filter.add_sensitive_word(word.word)

        questions = Question.select()
        return [q for q in questions if sensi_filter.check(json.loads(q.content)['content'])]

    def select_usergroup_by_user(self, user_name):
        return UserGroup.select().join(ReUserToGroup).join(User).where(User.username == user_name)

    def is_administrator(self, user_name):
        return User.select().where(User.username == user_name)[0].is_admin

    def get_question_by_group(self, target_group):
        return Question.select().join(ReQuestionToGroup).where(ReQuestionToGroup.group_id == target_group.id)

    def get_questiongroup_by_groupid(self, id):
        return QuestionGroup.select().where(QuestionGroup.id == id)

    def select_questionGroup_by_question(self, id):
        return QuestionGroup.select().join(ReQuestionToGroup). \
            join(Question).where(Question.id == id)

    def search_quesgroup_by_ques(self, s):
        result = "%".join(list(s))
        result = "%" + result + "%"
        questions = Question.select().where(Question.content % result)
        ans = []
        for item in questions:
            temp = self.select_questionGroup_by_question(item.id)
            for item2 in temp:
                if not item2 in ans:
                    ans.append(item2)
        return ans

    def searchUser(self, s):
        result = "%".join(list(s))
        result = "%" + result + "%"
        return User.select().where(User.username % result)

    def searchUserGroup(self, s):
        result = "%".join(list(s))
        result = "%" + result + "%"
        return UserGroup.select().where(UserGroup.group_name % result)

    def search_usergroup_bu_groupname(self, groupname):
        all = UserGroup.select()
        res = []
        for ug in all:
            # print("{} vs {}".format(groupname, ug.group_name))
            if (groupname in str(ug.group_name)):
                res.append(ug)
        return res

    def add_questiongroup(self, creator, groupname, ispublic, perms, tags):
        pregs = QuestionGroup.select().where(QuestionGroup.group_name == groupname)
        if len(pregs) > 0:
            return False, '问题组名重复'
        newQG = QuestionGroup(group_name=groupname, creator_id=creator.id, is_public=ispublic, tags=tags)
        newQG.save()
        if (ispublic == False):
            for permg in perms:
                pm = QuestionGroupPerm(user_group_id=permg.id, question_group_id=newQG.id)
                pm.save()
        return True, "成功创建问题组"

    def delete_questiongroup(self, questiongroup, username):
        user = self.get_user(username)
        isadmin = self.is_administrator(username)
        if isadmin or questiongroup.creator_id.id == user.id:
            questions = Question.select().join(ReQuestionToGroup).where(
                ReQuestionToGroup.group_id == questiongroup.id)
            if (len(questions) > 0):
                for question in questions:
                    question.delete_instance()
            questiongroup.delete_instance()
            return True, "成功删除"
        else:
            return False, "无权限"

    def delete_question(self, question, username):
        user = self.get_user(username)
        fatherGroup = QuestionGroup.select().join(ReQuestionToGroup).where(ReQuestionToGroup.question_id == question.id)
        if len(fatherGroup) != 0:
            group = fatherGroup[0]
        if len(fatherGroup) == 0 or self.is_administrator(username) or group.creator_id.id == user.id:
            question.delete_instance()
            return True, "成功删除问题"
        else:
            return False, "无权限"

    def questiongroup_is_visible_for(self, questiongroup, username):
        allowed = list(self.select_allowed_questiongroup_by_user(username))
        if self.is_administrator(username) or questiongroup.is_public or questiongroup in allowed:
            return True
        return False

    def get_usergroup_by_groupid(self, id):
        return UserGroup.select().where(UserGroup.id == id)

    def get_users_by_groupid(self, group_name):
        return User.select().join(ReUserToGroup).join(UserGroup).where(UserGroup.group_name == group_name)

    # creator_id = ForeignKeyField(User)
    # content = TextField()
    # question_name = CharField()
    def add_question(self, questiongroup, questionjson, username):
        user = self.get_user(username)
        question = Question(creator_id=user.id, content=json.dumps(questionjson), question_name=questionjson["name"])
        question.save()
        requestion = ReQuestionToGroup(question_id=question.id, group_id=questiongroup.id)
        requestion.save()
        return True, "成功添加"

    def add_usergroup(self, creator, groupname):
        group = UserGroup.select().where(UserGroup.group_name == groupname)
        if len(group) > 0:
            return False, '用户组名重复', group[0].id
        newQG = UserGroup(group_name=groupname, creator_id=creator.id)
        newQG.save()
        return True, "成功创建用户组", newQG.id

    def add_user_to_group(self, usergroup_id, user_id):
        reuser = ReUserToGroup(user_id=user_id, group_id=usergroup_id)
        reuser.save()
        return True, "成功添加"

    # user_id = ForeignKeyField(User)
    # question_id = ForeignKeyField(Question)
    # time = TimestampField()
    # score = FloatField()
    # user_answer = TextField()
    def add_solution_history(self, username, question_id, score, answer, t=None):
        if t == None:
            t = int(time.time())
        user = self.get_user(username)
        solution = SolutionHistory(user_id=user.id, question_id=question_id, time=t,
                                   score=score, user_answer=answer)
        solution.save()

    def recommend_questiongroup(self, username):
        '''根据用户名，获得一组推荐的问题组'''
        ret = []
        user_id = self.get_user(username).id
        # 先收集用户的历史做题tag
        history_questiongroups = (QuestionGroup.select()
                                  .join(ReQuestionToGroup)
                                  .join(SolutionHistory,
                                        on=(ReQuestionToGroup.question_id == SolutionHistory.question_id))
                                  .where(SolutionHistory.user_id == user_id)
                                  .distinct()
                                  .order_by(-SolutionHistory.time))
        # 确定候选的问题组。候选问题组指的是用户可以访问的，且用户还没有做过的问题组，按照id排序。
        candidates = list(self.select_allowed_questiongroup_by_user(username)) + list(
            self.select_public_questiongroup())
        candidates = set(candidates) - set(history_questiongroups)
        candidates = list(candidates)
        candidates = sorted(candidates, key=lambda e: e.id)
        tags = []
        # 我们遍历按照时间倒序遍历所有的历史做过的问题组，对于每一个tag，都去候选问题组里面查找是否有相同tag的问题组。如果有，则将其纳为推荐
        for hqg in history_questiongroups:
            for tag in json.loads(hqg.tags):
                if tag not in tags:
                    tags.append(tag)
                    # 查找包含有这个tag的且该学生没有做过且该学生有权限的问题组。使用倒序遍历，这样优先获得的是时间较近的问题组
                    for i in range(len(candidates) - 1, -1, -1):
                        if tag in json.loads(candidates[i].tags):
                            ret.append(candidates.pop(i))
        # 将剩下的候选问题组倒序添加进推荐问题组。
        for i in range(len(candidates) - 1, -1, -1):
            ret.append(candidates.pop(i))
        return ret

    def get_histroy(self, user_id):
        return SolutionHistory.select().where(SolutionHistory.user_id == user_id)

    def select_usergroup_allowed_by_questiongroup(self, qp):
        return UserGroup.select().join(QuestionGroupPerm).where(QuestionGroupPerm.question_group_id == qp.id)

    def remove_user(self, user_id, group_id):
        relation = ReUserToGroup.select().where(ReUserToGroup.user_id == user_id, ReUserToGroup.group_id == group_id)
        relation[0].delete_instance()
        return True, "成功删除用户"

    def remove_usergroup(self, group_id):
        group = UserGroup.select().where(UserGroup.id == group_id)
        group[0].delete_instance()
        return True, "成功删除用户组"

    def add_usergroup_to_questiongroup(self, ug, qg):
        re = QuestionGroupPerm(user_group_id=ug.id, question_group_id=qg.id)
        re.save()

    def delete_usergroup_to_questiongroup(self, ug, qg):
        re = QuestionGroupPerm.select().where(QuestionGroupPerm.question_group_id == qg.id and
                                              QuestionGroupPerm.user_group_id == ug.id)
        if len(re) > 0:
            re[0].delete_instance()

    def select_all_history(self):
        return SolutionHistory.select()

    def select_all_user(self):
        return User.select()

    def update_senstive_ques(self, id, new_content):
        q = Question.update({Question.content: new_content}).where(Question.id == id)
        q.execute()

manager = Manager()
if __name__ == '__main__':
    db.create_tables(
        [User, UserGroup, Question, QuestionGroup, SolutionHistory, ReUserToGroup, ReQuestionToGroup, QuestionGroupPerm,
         SensitiveWord])

# User.create_table()
# user = User(username = "lucas", password = "buaa2023")
# user.save()
# all = User.get()
# all.delete_instance()

# db.create_tables([UserGroup, QuestionGroup, SolutionHistory, ReUserToGroup, ReQuestionToGroup, QuestionGroupPerm])
